/**
 * 
 */
package dataPersistence.dao;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.Set;

import common.Result;
import common.ValueResult;

import dataPersistence.TransactionManager;
import dataPersistence.dto.ProductContainerDTO;
import dataPersistence.dto.ProductGroupDTO;
import dataPersistence.dto.StorageUnitDTO;

/**
 * @author fantastic4
 *
 */
public class StorageUnit_DB_DAO implements IStorageUnitDAO 
{


	/* (non-Javadoc)
	 * @see dataPersistence.dao.IStorageUnitDAO#read(dataPersistence.dto.StorageUnitDTO)
	 */
	@Override
	public Result read(StorageUnitDTO dto)
	{
		return null;
		// TODO Auto-generated method stub

	}


	/* (non-Javadoc)
	 * @see dataPersistence.dao.IStorageUnitDAO#add(dataPersistence.dto.StorageUnitDTO)
	 */
	@Override
	public Result add(ProductContainerDTO dto)
	{
		// TODO Auto-generated method stub
		
		Result result = new Result();
		try 
		{
			String query = "insert into ProductContainer (container_ID, name, parent) values (?, ?, ?)";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, dto.getProductContainerID());
			stmt.setString(2, dto.getName());
			stmt.setString(3, dto.getParentID());
			
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not insert Storage Unit");

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		return result;

	}


	/* (non-Javadoc)
	 * @see dataPersistence.dao.IStorageUnitDAO#update(dataPersistence.dto.StorageUnitDTO)
	 */
	@Override
	public Result update(ProductContainerDTO dto)
	{
		// TODO Auto-generated method stub
		
		Result result = new Result();
		try 
		{
			String query = "update ProductContainer set name=?,parent=? where container_ID =?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(3, dto.getProductContainerID());
			stmt.setString(1, dto.getName());
			stmt.setString(2, dto.getParentID());
			
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not update Storage Unit");

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		return result;

	}


	/* (non-Javadoc)
	 * @see dataPersistence.dao.IStorageUnitDAO#delete(dataPersistence.dto.StorageUnitDTO)
	 */
	@Override
	public Result delete(ProductContainerDTO dto)
	{
		// TODO Auto-generated method stub
		
		Result result = new Result();
		try
		{
			String query = "delete from ProductContainer where container_ID =?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, dto.getProductContainerID());
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not delete Storage Unit");
		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		return result;

	}


	public ValueResult<Set<StorageUnitDTO>> getAllStorageUnits()
	{
		// TODO Auto-generated method stub
		ValueResult<Set<StorageUnitDTO>> result = new ValueResult<Set<StorageUnitDTO>>();

		PreparedStatement stmt = null;
		ResultSet rs=null;
		Set<StorageUnitDTO> allStorageUnits = new HashSet<StorageUnitDTO>();
		try 
		{
			String query = "select container_ID, name, parent from ProductContainer where parent is null";
		//	stmt.setString(1,null);
			
			stmt = TransactionManager.getConnection().prepareStatement(query);

			rs = stmt.executeQuery();
			while (rs.next())
			{
				StorageUnitDTO tmp = new StorageUnitDTO();
				tmp.setProductContainerID(rs.getString(1));
				tmp.setName(rs.getString(2));
				tmp.setParentID(rs.getString(3));
				
				allStorageUnits.add(tmp);
			}
			result.setValue(allStorageUnits);
			result.setStatus(true);

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally {
			TransactionManager.safeClose(rs);
			TransactionManager.safeClose(stmt);
		}

		return result;		

	}
	
	public ValueResult<ArrayList<ProductContainerDTO>> getChildren(String string)
	{
		ValueResult<ArrayList<ProductContainerDTO>> result = new ValueResult<ArrayList<ProductContainerDTO>>();

		PreparedStatement stmt = null;
		ResultSet rs=null;
		ArrayList<ProductContainerDTO> list = new ArrayList<ProductContainerDTO>();
		try 
		{
			String query = "select * from ProductContainer AS pc JOIN ProductGroup AS pg ON pc.container_ID=pg.productContainer_ID WHERE pc.parent=? ";
			stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1,string);
			
			rs = stmt.executeQuery();
			while (rs.next())
			{
				
				ProductContainerDTO tmp;
				if(rs.getObject("threeMonthSupply")!=null)
				{
					tmp= new ProductGroupDTO();
					tmp.setName(rs.getString("name"));
					tmp.setParentID(string);
					tmp.setProductContainerID(rs.getString("container_ID"));
					((ProductGroupDTO) tmp).setThreeMonthUnit(rs.getString("threeMonthSupplyUnit"));
					((ProductGroupDTO) tmp).setThreeMonthsSize(rs.getInt("threeMonthSupply"));
					
				}
				else
				{
					tmp= new StorageUnitDTO();
					tmp.setName(rs.getString("name"));
					tmp.setParentID(string);
					tmp.setProductContainerID(rs.getString("container_ID"));	
				}
				list.add(tmp);
			}
			result.setValue(list);
			result.setStatus(true);

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}		
		finally {
			TransactionManager.safeClose(rs);
			TransactionManager.safeClose(stmt);
		}

		return result;		

	}

}
